package com.dfd.store.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.dfd.store.dao.ProductDao;
import com.dfd.store.domain.Product;
import com.dfd.store.utils.JDBCUtils;

public class ProductDaoImpl implements ProductDao {
	QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());

	@Override
	public List<Product> findNews() throws Exception {
		String sql = "select * from product  where pflag = 0 ORDER BY pdate desc limit 0,9";
		return runner.query(sql, new BeanListHandler<Product>(Product.class));

	}

	@Override
	public List<Product> findHots() throws Exception {
		String sql = "select * from product where pflag =0 and is_hot = 1 ORDER BY pdate desc limit 0, 9";
		return runner.query(sql, new BeanListHandler<Product>(Product.class));
	}

	@Override
	public Product findProductById(String pid) throws Exception {
		String sql = "select * from product where pid = ?";
		return runner.query(sql, new BeanHandler<Product>(Product.class), pid);
	}

	@Override
	public int findTotalRecords(String cid) throws Exception {

		String sql = "select count(*) from product where cid = ?";
		// ScalarHandler: 可以返回指定列的一个值或返回一个统计函数的值.
		Long sum = (Long) runner.query(sql, new ScalarHandler(), cid);
		return sum.intValue();
	}

	@Override
	public List findProductByCidWithPage(String cid, int startIndex, int pageSize) throws Exception {
		String sql = "select * from product where cid = ? order by pdate desc limit ?,?";
		return runner.query(sql, new BeanListHandler<Product>(Product.class), cid, startIndex, pageSize);

	}

	@Override
	public int findTotalRecords() throws Exception {
		String sql = "select count(*) from product";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}

	@Override
	public List findProductByCidWithPage(int startIndex, int pageSize) throws Exception {
		String sql = "select * from product limit ?,?";
		return runner.query(sql, new BeanListHandler<Product>(Product.class), startIndex, pageSize);

	}

	@Override
	public List findDownWithPage(int startIndex, int pageSize) throws Exception {
		String sql = "select * from product where pflag = 1 limit ?,?";
		return runner.query(sql, new BeanListHandler<Product>(Product.class), startIndex, pageSize);

	}

	@Override
	public void UpProduct(String pid) throws Exception {
		String sql = "update product set pflag = 0 where pid = ? ";
		runner.update(sql, pid);

	}

	@Override
	public void addProduct(Product product) throws Exception {
		String sql = " insert into product values (?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { product.getPid(), product.getPname(), product.getMarket_price(), product.getShop_price(),
				product.getPimage(), product.getPdate(), product.getIs_hot(), product.getPdesc(), product.getPflag(),
				product.getCid() };
		runner.update(sql, params);
	}

	@Override
	public void DownProductByid(String pid) throws Exception {
		String sql = "update product set pflag = 1 where pid = ? ";
		runner.update(sql, pid);
	}

}
